package com.ruoyi.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class ExcelMergerUtil {
    /**
     * 从模板文件中检测表头行数
     * @param templateFile 模板文件路径
     * @param sheetIndex 工作表索引(从0开始)
     * @return 表头行数
     */
    public static int detectHeaderRowCount(String templateFile, int sheetIndex) throws IOException {
        try (FileInputStream fis = new FileInputStream(templateFile);
            Workbook workbook = WorkbookFactory.create(fis)) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            return detectHeaderRowCount(sheet);
        }
    }
    /**
     * 从工作表中检测表头行数
     * @param sheet 工作表对象
     * @return 表头行数
     */
    public static int detectHeaderRowCount(Sheet sheet) {
        int headerRowCount = 0;

        // 检测逻辑：连续的非空行且包含文本的被认为是表头
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                // 空行可能表示表头结束
                break;
            }

            boolean isHeaderRow = false;
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String value = cell.getStringCellValue().trim();
                    if (!value.isEmpty()) {
                        isHeaderRow = true;
                        break;
                    }
                }
            }

            if (isHeaderRow) {
                headerRowCount++;
            } else {
                // 遇到非表头行，停止检测
                break;
            }
        }

        return headerRowCount;
    }
    public static void mergeExcelFiles(List<String> inputFiles, String templateFile, String outputFile) throws IOException {
        // 1. 从模板文件检测表头行数
        int headerRowCount = detectHeaderRowCount(templateFile, 0);
        System.out.println("检测到表头行数: " + headerRowCount);

        // 2. 创建新的工作簿
        Workbook outputWorkbook = new XSSFWorkbook();

        // 3. 遍历所有输入文件
        boolean isFirstFile = true;
        for (String inputFile : inputFiles) {
            try (FileInputStream fis = new FileInputStream(inputFile);
                 Workbook inputWorkbook = WorkbookFactory.create(fis)) {

                // 4. 遍历工作表
                for (int i = 0; i < inputWorkbook.getNumberOfSheets(); i++) {
                    Sheet inputSheet = inputWorkbook.getSheetAt(i);
                    String sheetName = inputWorkbook.getSheetName(i);

                    // 5. 在输出工作簿中创建/获取工作表
                    Sheet outputSheet = outputWorkbook.getSheet(sheetName);
                    boolean isNewSheet = (outputSheet == null);

                    if (isNewSheet) {
                        outputSheet = outputWorkbook.createSheet(sheetName);
                    }

                    // 6. 处理表头和数据
                    if (isFirstFile && isNewSheet) {
                        // 第一个文件的新工作表：复制表头+全部数据
                        copyHeaderRows(inputSheet, outputSheet, outputWorkbook, headerRowCount);
                        copyDataRows(inputSheet, outputSheet, outputWorkbook,
                                headerRowCount, outputSheet.getLastRowNum() + 1);
                    } else if (isFirstFile) {
                        // 第一个文件的已有工作表：只追加数据(含表头后的数据)
                        copyDataRows(inputSheet, outputSheet, outputWorkbook,
                                headerRowCount, outputSheet.getLastRowNum() + 1);
                    } else {
                        // 非第一个文件：只复制表头之后的数据
                        copyDataRows(inputSheet, outputSheet, outputWorkbook,
                                headerRowCount, outputSheet.getLastRowNum() + 1);
                    }
                }
            }
            isFirstFile = false;
        }

        // 7. 写入输出文件
        try (FileOutputStream fos = new FileOutputStream(outputFile)) {
            outputWorkbook.write(fos);
        }

        outputWorkbook.close();
    }


    private static void copyHeaderRows(Sheet sourceSheet, Sheet targetSheet, Workbook targetWorkbook, int headerRowCount) {
        for (int i = 0; i < headerRowCount; i++) {
            Row sourceRow = sourceSheet.getRow(i);
            if (sourceRow != null) {
                Row targetRow = targetSheet.createRow(i);
                copyRow(sourceRow, targetRow, targetWorkbook);
            }
        }
    }

    private static void copyDataRows(Sheet sourceSheet, Sheet targetSheet,
                                     Workbook targetWorkbook, int headerRowCount,
                                     int targetStartRow) {
        // 始终从headerRowCount开始复制(跳过表头)
        for (int i = headerRowCount; i <= sourceSheet.getLastRowNum(); i++) {
            Row sourceRow = sourceSheet.getRow(i);
            if (sourceRow != null) {
                Row targetRow = targetSheet.createRow(targetStartRow + i - headerRowCount);
                copyRow(sourceRow, targetRow, targetWorkbook);
            }
        }
    }

    private static void copyRow(Row sourceRow, Row targetRow, Workbook targetWorkbook) {
        for (int j = 0; j < sourceRow.getLastCellNum(); j++) {
            Cell sourceCell = sourceRow.getCell(j);
            if (sourceCell != null) {
                Cell targetCell = targetRow.createCell(j);
                copyCell(sourceCell, targetCell, targetWorkbook);
            }
        }
    }
    private static void copyCell(Cell inputCell, Cell outputCell, Workbook outputWorkbook) {
        // 设置单元格样式
        CellStyle newCellStyle = outputWorkbook.createCellStyle();
        CellStyle inputCellStyle = inputCell.getCellStyle();

        // 复制样式属性
        newCellStyle.cloneStyleFrom(inputCellStyle);

        // 应用新样式
        outputCell.setCellStyle(newCellStyle);

        // 根据单元格类型复制数据
        switch (inputCell.getCellType()) {
            case STRING:
                outputCell.setCellValue(inputCell.getStringCellValue());
                break;
            case NUMERIC:
                outputCell.setCellValue(inputCell.getNumericCellValue());
                break;
            case BOOLEAN:
                outputCell.setCellValue(inputCell.getBooleanCellValue());
                break;
            case FORMULA:
                outputCell.setCellFormula(inputCell.getCellFormula());
                break;
            case BLANK:
                outputCell.setBlank();
                break;
            default:
                outputCell.setCellValue(inputCell.toString());
        }
    }
}
